<?php if (!defined('BASEPATH')) exit('No direct script access allowed');
class Employee_Leaves_db extends CI_Model
{
	function getEmployee_Leaves($data=false)
	{
		if($data)
			$this->db->where($data);
		$this->db->join("leaves b","b.leave_id = a.leave_id", "inner");
		$query = $this->db->get('employee_leaves a');
		return $query->result_array();
	}
	
	function save($data, $item=false)
	{	
		if (!$item)
		{			
			$this->db->trans_start();
			$this->db->insert('employee_leaves', $data);
			$item = $this->db->insert_id();
			$this->db->trans_complete(); 					
		}
		else
		{
			$this->db->trans_start();
			$this->db->where('employee_leave_id', $item);
			$this->db->update('employee_leaves', $data);		
			$this->db->trans_complete();
		}
		return $item;	
	}
	
	function delete($id)
	{
		if(count($id)>1)
			$this->db->where_in('employee_leave_id',$id);
		else
			$this->db->where('employee_leave_id',$id[0]);

		$this->db->delete('employee_leaves'); 
		return $id;
	}
	
	function getList()
	{		
		$sortname = $this->input->post('sortname');
		$sortorder = $this->input->post('sortorder');
		$page = $this->input->post('page');
		$rp = $this->input->post('rp');		
		
		//$query = $this->input->post('query');
		//$qtype = $this->input->post('qtype');
		$squery = $this->input->post('squery');
		
		$cond = "";

		if (!$sortname) $sortname = 'b.fullname';
		if (!$sortorder) $sortorder = 'ASC';
		
		
		if (!$page) $page = 1;
		if (!$rp) $rp = 40;
		
		$start = (($page-1) * $rp);
		
		$this->db->start_cache();
		if ($squery) $this->db->where("(LOWER(b.fullname) LIKE '%$squery%' OR LOWER(b.employee_no) LIKE '%$squery%' OR LOWER(b.username) LIKE '%$squery%')");
		$this->db->where('b.is_deleted = 0');
		$this->db->select("b.id as pkey, b.id as test, b.employee_no, b.fullname, c.es_name, GROUP_CONCAT(' ',d.leave_code,'(', a.available_days ,')' ORDER BY d.leave_name) as available_leaves", FALSE);
		$this->db->join("users b","b.id = a.user_id", "inner");
		$this->db->join("employee_status c","c.employee_status_id = b.employee_status_id", "left");
		$this->db->join("leaves d","d.leave_id = a.leave_id", "left");
		$this->db->group_by('b.id');
		$this->db->order_by($sortname,$sortorder);
		$this->db->limit($rp, $start);		
		$query = $this->db->get("employee_leaves a");
		//$num = $this->db->count_all_results();
		$num = count($query->result_array());

		$this->db->flush_cache();
		
		
		$rows = array();
		if (isset($query))
		{			 
			foreach($query->result_array() as $row):			
				$key = key($row);
				$id = $row[$key];
				unset($row[$key]);
				$cell = array();
				foreach ($row as $item) { $cell[] = $item; }
				$rows[] = array( "id" => $id, "cell" => $cell );				
			endforeach; 
		}
		
		$data['rows'] = $rows;
		$data['page'] = $page;
		$data['total'] = $num;
		return $data;		
		
	}
	
	function getEmployeeLeavesList($userid)
	{		
		$sortname = $this->input->post('sortname');
		$sortorder = $this->input->post('sortorder');
		$page = $this->input->post('page');
		$rp = $this->input->post('rp');		
		
		//$query = $this->input->post('query');
		//$qtype = $this->input->post('qtype');
		$squery = $this->input->post('squery');
		
		$cond = "";

		if (!$sortname) $sortname = 'b.leave_name';
		if (!$sortorder) $sortorder = 'ASC';
		
		
		if (!$page) $page = 1;
		if (!$rp) $rp = 40;
		
		$start = (($page-1) * $rp);
		
		$this->db->start_cache();
		$this->db->select("a.employee_leave_id as pkey, a.employee_leave_id as test, b.leave_code, b.leave_name, a.available_days");
		$this->db->join("leaves b","a.leave_id = b.leave_id", "inner");
		$this->db->where('a.user_id',$userid);
		$this->db->order_by($sortname,$sortorder);
		$this->db->limit($rp, $start);		
		$query = $this->db->get("employee_leaves a");
		//$num = $this->db->count_all_results();
		$num = count($query->result_array());

		$this->db->flush_cache();
		
		
		$rows = array();
		if (isset($query))
		{			 
			foreach($query->result_array() as $row):			
				$key = key($row);
				$id = $row[$key];
				unset($row[$key]);
				$cell = array();
				foreach ($row as $item) { $cell[] = $item; }
				$rows[] = array( "id" => $id, "cell" => $cell );				
			endforeach; 
		}
		
		$data['rows'] = $rows;
		$data['page'] = $page;
		$data['total'] = $num;
		return $data;		
		
	}
	
	function getAvailableLeaves($userid)
	{
		$this->db->select("a.leave_id, a.leave_code, a.leave_name");
		$this->db->where("a.leave_id NOT IN (SELECT leave_id FROM employee_leaves WHERE user_id = $userid)");
		$query = $this->db->get("leaves a");
		return $query->result_array();
	}

}

?>